package com.xy;

/**
 * @author Ybao
 */

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

public class UtilDB {
    private Object lock = new Object();
    private static final String TAG = "SqLiteDao";
    private SQLiteOpenHelper helper = null;
    private SQLiteDatabase db = null;

    //	private Field[] fields = null;
    Context context;

    /***/
    public UtilDB(Context context) {
        this.context = context.getApplicationContext();

    }

    /**
     * 打开数据库操作
     */
    public void open(String dbname) {
        if (helper == null) {
            synchronized (lock) {
                if (helper == null) {
                    helper = new SQLiteOpenHelper(context, dbname, null, 1) {
                        @Override
                        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                        }

                        @Override
                        public void onCreate(SQLiteDatabase db) {
                        }
                    };
                    // SQLiteDatabase对象
                    db = helper.getWritableDatabase();
                }
            }
        }
    }

    /**
     * 打开数据库操作
     */
    public void open(SQLiteOpenHelper mhelper) {
        if (db == null) {
            synchronized (lock) {
                if (db == null) {
                    helper = mhelper;
                    // SQLiteDatabase对象
                    db = helper.getWritableDatabase();
                }
            }
        }
    }

    /**
     * 关闭数据库操作
     */
    public void close() {
        if (helper != null) {
            synchronized (lock) {
                if (helper != null) {
                    db.close();
                    helper.close();
                    db = null;
                    helper = null;
                }
            }
        }
    }

    /**
     * 返回数据库对象，可以自主的使用一些原生方法
     */
    public SQLiteDatabase getSqliteDB() {
        return db;
    }

    /**
     * 查看数据库中是否存在某表
     */
    public boolean hasTable(String table_name) {
        boolean result = false;
        Cursor cur = null;
        try {
            String sql_table = "select count(*) as c from Sqlite_master  where type='table' and name ='"
                    + table_name.trim() + "'";
            cur = db.rawQuery(sql_table, null);
            if (cur.moveToNext()) {
                int count = cur.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }
            cur.close();
        } catch (Exception e) {
            return result;
        }
        return result;
    }

    /**
     * 设定table的名称，和其适配的javabean,如果该表不存在将根据这两个参数建立该表
     *
     * @param tableName 数据表名称
     * @param adpater   该数据表对应的javabean 注意，避免使用基本数据类型和数组
     */
    public void createTables(String tableName, Class<?> adpater) {
        synchronized (lock) {
            Field[] fields = adpater.getDeclaredFields();
            if (!hasTable(tableName)) {
                StringBuffer createSql = new StringBuffer("CREATE TABLE " + tableName + "(");
                for (int i = 0, len = fields.length; i < len; i++) {
                    Field f = fields[i];
                    String fieldName = f.getName();

                    if ("$change".equalsIgnoreCase(fieldName)) {
                        continue;
                    }
                    if (i > 0) {
                        createSql.append(",");
                    }

                    String fieldType = getSqliteTypeClass(f.getType()).getSimpleName();
                    createSql.append(" " + fieldName + " " + fieldType + " ");
                    if (f.getAnnotation(PrimaryKey.class) != null) {
                        createSql.append(" PRIMARY KEY  AUTOINCREMENT  NOT NULL ");
                        //					createSql.append(" PRIMARY KEY  NOT NULL ");
                    }
                }
                createSql.append(")");
                Log.d(TAG, "创建表：" + createSql.toString());
                db.execSQL(createSql.toString());
            }
        }
    }

    /**
     * 删除数据库中的表
     */
    public void delTable(String tablename) {
        synchronized (lock) {
            if (hasTable(tablename)) {
                db.execSQL("DROP TABLE " + tablename);
            }
        }
    }

    /**
     * 删除表中的所有数据
     */
    public void clearTable(String tablename) {
        synchronized (lock) {
            if (hasTable(tablename)) {
                db.execSQL("delete from " + tablename);
            }
        }
    }

    /**
     * 通用查询接口,无返回值
     */
    public void execSQL(String sql) {
        db.execSQL(sql);
    }

    /**
     * 查询接口,带返回值
     */
    public Cursor rawQuery(String sql, String ...args) {
        return db.rawQuery(sql, args);
    }

    /**
     * 增加记录,并返回增加记录的索引,否则返回-1
     *
     * @param bean table表对应的bean
     * @return long 插入字段的索引
     */
    public long insertRow(String tableName, Object bean) {
        Long ret = -1L;
        synchronized (lock) {
            if (hasTable(tableName) && bean != null) {
                ContentValues cv = BeanAdpater(bean, false);
                ret = db.insert(tableName, "", cv);
            } else {
                Log.d(TAG, "参数为空或者类型错误");
            }
        }
        return ret;
    }

    /**
     * 根据查询条件返回数据表中的记录数组arrylist
     *
     * @param tableName
     * @param where
     * @param adpater   查询的条件语句,将补在全部查询sql语句之后
     */
    public <T> List<T> getRows(String tableName, String where, Class<T> adpater) {
        String sql = "select * from " + tableName;
        if (!TextUtils.isEmpty(where)) {
            sql += " where " + where;
        }
        Cursor cursor = db.rawQuery(sql, null);
        Log.d(TAG, "select查询：数据总行数：" + cursor.getCount() + "；列数:" + cursor.getColumnNames().length);
        return changeRowsToBean(cursor, adpater);
    }


    /**
     * 根据查询条件返回数据表中的记录数组arrylist
     *
     * @param sql
     * @param adpater 查询的条件语句,将补在全部查询sql语句之后
     */
    public <T> List<T> getRows(String sql, Class<T> adpater) {
        Cursor cursor = db.rawQuery(sql, null);
        Log.d(TAG, "select查询：数据总行数：" + cursor.getCount() + "；列数:" + cursor.getColumnNames().length);
        return changeRowsToBean(cursor, adpater);
    }

    /**
     * 根据查询条件返回数据表中的记录数组arrylist
     *
     * @param tableName
     * @param where
     * @param adpater   查询的条件语句,将补在全部查询sql语句之后
     */
    public <T> T getRow(String tableName, String where, Class<T> adpater) {
        String sql = "select * from " + tableName;
        if (!TextUtils.isEmpty(where)) {
            sql += " where " + where;
        }
        Cursor cursor = db.rawQuery(sql, null);
        Log.d(TAG, "select查询：数据总行数：" + cursor.getCount() + "；列数:" + cursor.getColumnNames().length);
        cursor.moveToFirst();

        T bean = null;
        try {
            bean = changeRowToBean(cursor, adpater);
        } catch (Exception e) {
            Log.e(TAG, e.getMessage());
        }
        cursor.close();
        return bean;
    }


    /**
     * 根据查询条件返回数据表中的记录数组arrylist
     *
     * @param sql
     * @param adpater 查询的条件语句,将补在全部查询sql语句之后
     */
    public <T> T getRow(String sql, Class<T> adpater) {
        Cursor cursor = db.rawQuery(sql, null);
        Log.d(TAG, "select查询：数据总行数：" + cursor.getCount() + "；列数:" + cursor.getColumnNames().length);
        cursor.moveToFirst();

        T bean = null;
        try {
            bean = changeRowToBean(cursor, adpater);
        } catch (Exception e) {
            Log.e(TAG, e.getMessage());
        }
        cursor.close();
        return bean;
    }

    public void updateRow(String tableName, Object bean, String where, String[] whereArg) {
        synchronized (lock) {
            if (hasTable(tableName) && bean != null) {
                ContentValues cv = BeanAdpater(bean, false);
                db.update(tableName, cv, where, whereArg);
            } else {
                Log.d(TAG, "参数为空或者类型错误");
            }
        }
    }

    /**
     * 删除指定的bean记录，依据本类的bean类设置的主键的值，所以主键对应的成员变量的值必须存在
     */
    public void deleteRow(String tableName, Object bean, String where, String[] whereArg) {
        synchronized (lock) {
            if (hasTable(tableName) && bean != null) {
                db.delete(tableName, where, whereArg);
            } else {
                Log.d(TAG, "参数为空或者类型错误");
            }
        }
    }

    /**
     * bean 转换为 ContentValues
     *
     * @param covertPrimaryKey 是否包含主键的Key
     */
    private ContentValues BeanAdpater(Object bean, boolean covertPrimaryKey) {
        ContentValues cv = new ContentValues();
        Field[] fields = bean.getClass().getDeclaredFields();
        for (int i = 0, len = fields.length; i < len; i++) {
            Field f = fields[i];
            if (!covertPrimaryKey && f.getAnnotation(PrimaryKey.class) != null) {
                continue;
            }

            String fieldName = f.getName();
            if ("$change".equalsIgnoreCase(fieldName)) {
                continue;
            }

            f.setAccessible(true);
            try {
                String returntype = getSqliteTypeClass(f.getType()).getSimpleName().toLowerCase(Locale.US);
                Object val = f.get(bean);
                if (val == null) {
                    continue;
                }
                if (returntype.equals("string")) {
                    cv.put(fieldName, val.toString());
                } else if (returntype.equals("character")) {
                    cv.put(fieldName, val.toString());
                } else if (returntype.equals("boolean")) {
                    cv.put(fieldName, (Boolean) val);
                } else if (returntype.equals("int")) {
                    cv.put(fieldName, (Integer) val);
                } else if (returntype.equals("byte")) {
                    cv.put(fieldName, (Byte) val);
                } else if (returntype.equals("short")) {
                    cv.put(fieldName, (Short) val);
                } else if (returntype.equals("double")) {
                    cv.put(fieldName, (Double) val);
                } else if (returntype.equals("float")) {
                    cv.put(fieldName, (Float) val);
                } else if (returntype.equals("long")) {
                    cv.put(fieldName, (Long) val);
                } else {
                    cv.putNull(fieldName);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return cv;
    }

    /**
     * 获取当前指针所在位置的一行数据
     *
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @throws IllegalArgumentException
     */
    public <T> T changeRowToBean(Cursor cursor, Class<T> adpater) throws IllegalAccessException, InstantiationException,
            IllegalArgumentException, SecurityException, NoSuchFieldException {
        T bean = null;
        if (!cursor.isClosed() && !cursor.isAfterLast()) {
            bean = adpater.newInstance();
            for (int i = 0, len = cursor.getColumnCount(); i < len; i++) {
                Field f = adpater.getDeclaredField(cursor.getColumnName(i));
                f.setAccessible(true);
                String returntype = getJavaTypeClass(f.getType()).getSimpleName().toLowerCase(Locale.US);
                String val = cursor.getString(i);
                if (val == null) {
                    continue;
                }
                Object oval = null;
                if (returntype.equals("string")) {
                    oval = val;
                } else if (returntype.equals("character")) {
                    oval = (val.charAt(0));
                } else if (returntype.equals("boolean")) {
                    oval = val.equals("1") ? true : false;
                } else if (returntype.equals("int")) {
                    oval = Integer.parseInt(val);
                } else if (returntype.equals("byte")) {
                    oval = Byte.parseByte(val);
                } else if (returntype.equals("short")) {
                    oval = Short.parseShort(val);
                } else if (returntype.equals("double")) {
                    oval = Double.parseDouble(val);
                } else if (returntype.equals("float")) {
                    oval = Float.parseFloat(val);
                } else if (returntype.equals("long")) {
                    oval = Long.parseLong(val);
                }
                f.set(bean, oval);
            }
        }
        return bean;
    }

    /**
     * 获取当前指针所在位置的一行数据
     *
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @throws IllegalArgumentException
     */
    public <T> List<T> changeRowsToBean(Cursor cursor, Class<T> adpater) {
        List<T> rows = new ArrayList<T>();
        if (!cursor.isClosed()) {
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                try {
                    T bean = changeRowToBean(cursor, adpater);
                    if (bean != null) {
                        rows.add(bean);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
                cursor.moveToNext();
            }
            cursor.close();
        }
        return rows;
    }

    /**
     * 获取传入类型的非基本数据类型表示方式
     */
    private Class<?> getSqliteTypeClass(Class<?> classz) {

        return classz.isPrimitive() ? getBasicClass(classz) : String.class;
    }

    private Class<?> getJavaTypeClass(Class<?> classz) {

        return classz.isPrimitive() ? classz : String.class;
    }

    /**
     * 获得包装类
     *
     * @param typeClass
     * @return
     */
    @SuppressWarnings("all")
    public static Class<? extends Object> getBasicClass(Class typeClass) {
        Class _class = basicMap.get(typeClass);
        if (_class == null)
            _class = typeClass;
        return _class;
    }

    private static Map<Class<?>, Class<?>> basicMap = new HashMap<Class<?>, Class<?>>();

    static {
        basicMap.put(int.class, Integer.class);
        basicMap.put(long.class, Long.class);
        basicMap.put(float.class, Float.class);
        basicMap.put(double.class, Double.class);
        basicMap.put(boolean.class, Boolean.class);
        basicMap.put(byte.class, Byte.class);
        basicMap.put(short.class, Short.class);
    }

    @Target({ElementType.FIELD})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface PrimaryKey {
    }
}
